import pandas as pd
import matplotlib.pyplot as plt
from statsmodels.tsa.stattools import adfuller, kpss
from statsmodels.graphics.tsaplots import plot_acf, plot_pacf


def perform_stationarity_tests(excel_file, date_col_index=0, data_col_index=3, max_diff_order=5):
    """
    执行时间序列数据的平稳性检验，并自动检测需要几阶差分才能使数据平稳。

    Args:
        excel_file (str): Excel 文件的路径 (.xlsx).
        date_col_index (int): 日期列的索引（从 0 开始，默认为第一列）.
        data_col_index (int): 数据列的索引（从 0 开始，默认为第四列）.
        max_diff_order (int): 最大差分阶数（默认为 5）.

    Returns:
        int: 需要的差分阶数，如果超过最大差分阶数仍然不平稳，返回 -1.
    """
    # 1. 读取数据
    df = pd.read_excel(excel_file, header=None)
    df.columns = [f'col{i}' for i in range(len(df.columns))]
    df.rename(columns={df.columns[date_col_index]: '日期'}, inplace=True)
    df['日期'] = pd.to_datetime(df['日期'], format='%Y/%m/%d', errors='coerce')
    df = df.dropna(subset=['日期'])
    df.set_index('日期', inplace=True)
    data = df.iloc[:, data_col_index].dropna()

    # 2. 初始数据
    data_diff = data
    diff_order = 0

    # 3. 循环进行差分，直到 ADF 检验表明数据平稳，或者超过最大阶数
    for diff_order in range(max_diff_order + 1):

        if diff_order > 0:
            data_diff = data_diff.diff().dropna()

        print(f"\nAfter {diff_order} order differencing:")

        # 绘制时间序列图
        plt.figure(figsize=(10, 4))
        plt.plot(data_diff)
        plt.title(f"Time Series Plot after {diff_order} Order Differencing")
        plt.show()

        # 绘制 ACF 和 PACF 图
        plt.figure(figsize=(12, 6))
        plot_acf(data_diff, lags=40, ax=plt.subplot(211))
        plot_pacf(data_diff, lags=40, ax=plt.subplot(212))
        plt.tight_layout()
        plt.show()

        # 4. ADF 检验
        print("ADF Test:")
        adf_result = adfuller(data_diff)
        print(f"  ADF Statistic: {adf_result[0]:.4f}")
        print(f"  p-value: {adf_result[1]:.4f}")
        print("  Critical Values:")
        for key, value in adf_result[4].items():
            print(f"    {key}: {value:.4f}")
        if adf_result[1] <= 0.05:
            print(f"  ADF test indicates that the data is likely stationary after {diff_order} order differencing.")
            break
        else:
            print(f"  ADF test indicates that the data is likely non-stationary after {diff_order} order differencing.")

    if adf_result[1] > 0.05 and diff_order == max_diff_order:
        print(f"\nWarning: After a maximum of {max_diff_order} order differencing, the data is still not stationary.")
        return -1

    # 5. KPSS 检验
    print("\nKPSS Test:")
    kpss_result = kpss(data_diff, regression='c', nlags='auto')
    print(f"  KPSS Statistic: {kpss_result[0]:.4f}")
    print(f"  p-value: {kpss_result[1]:.4f}")
    print("  Critical Values:")
    for key, value in kpss_result[3].items():
        print(f"    {key}: {value:.4f}")
    if kpss_result[1] < 0.05:
        print("  KPSS test indicates the time series is likely non-stationary.")
    else:
        print("  KPSS test indicates the time series is likely stationary.")

    return diff_order if adf_result[1] <= 0.05 else -1


if __name__ == '__main__':
    excel_file_path = 'output.xlsx'  # 替换成你的文件路径
    diff_order = perform_stationarity_tests(excel_file_path)
    if diff_order != -1:
        print(f"\nRecommended differencing order for modeling: {diff_order}.")
    else:
        print(
            "\nWarning: Unable to make the data stationary through differencing. Please check the data or try other methods.")